package com.tjtt.gwm.dao.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.tjtt.gwm.dao.JunyansDao;
import com.tjtt.tdm.base.BaseHibernateDao;
import com.tjtt.tdm.base.PageResult;

public class JunyansDaoImpl extends BaseHibernateDao implements JunyansDao {
	public PageResult queryJunyans(Map filterMap){
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_junyans t1  where t1.id not in (select t.main_id from flow_task t )  " );
		queryString.append("select t1.ID,t1.renwubh,t1.chanpindh,t1.shiyanlx,t1.fuzher,t1.shiyandd,t1.shemidj,t1.shiyankssj,t1.yujijssj,t1.jieduanlx," +
				"t1.jihuaydl from kl_junyans t1  where t1.id not in (select t.main_id from flow_task t ) " );
		if(null!=filterMap && !filterMap.isEmpty()){
			if(null!=filterMap.get("renwubh") && !"".equals(filterMap.get("renwubh"))){
				queryString.append(" and t1.renwubh like '%"+filterMap.get("renwubh")+"%'");
				countString.append(" and t1.renwubh like '%"+filterMap.get("renwubh")+"%'");
			}
			if(null!=filterMap.get("chanpindh") && !"".equals(filterMap.get("chanpindh"))){
				queryString.append(" and t1.chanpindh like '%"+filterMap.get("chanpindh")+"%'");
				countString.append(" and t1.chanpindh like '%"+filterMap.get("chanpindh")+"%'");
			}
			if(null!=filterMap.get("jieduanlx") && !"".equals(filterMap.get("jieduanlx"))){
				queryString.append(" and t1.jieduanlx like '%"+filterMap.get("jieduanlx")+"%'");
				countString.append(" and t1.jieduanlx like '%"+filterMap.get("jieduanlx")+"%'");
			}
		}
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	public PageResult queryJunyansshejis(Map filterMap){
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		if (filterMap.get("junyansid")==null){
			filterMap.put("junyansid", "-1");
		}
		countString.append("select count(*) from kl_junyans_shejis t1 " +
				"left join sys_users t2 on t1.userid=t2.userid " +
				"left join sys_organization t3 on t2.org_id=t3.org_id  where 1=1");
		queryString.append("select t1.id,t2.username,t2.sex,t2.zhichengname,t2.zhiwuname,t3.org_no,t1.userid from kl_junyans_shejis t1 " +
				"left join sys_users t2 on t1.userid=t2.userid " +
				"left join sys_organization t3 on t2.org_id=t3.org_id  where 1=1 ");
		queryString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		countString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	public PageResult queryJunyansItem(Map filterMap){
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		if (filterMap.get("junyansid")==null){
			filterMap.put("junyansid", "-1");
		}
		countString.append("select count(*) from kl_junyans_xm t1 " +
				"left join kl_ceshixm t2 on t1.xiangmuid=t2.id where 1=1");
		queryString.append("select t1.id,t2.xiangmumc,t2.biaozunyq,t2.danwei,t2.shebeiyq,t1.jieguo,t1.ysjlpath,t1.reportpath from kl_junyans_xm t1 " +
				"left join kl_ceshixm t2 on t1.xiangmuid=t2.id where 1=1 " );
		queryString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		countString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}

	@Override
	public PageResult querySelectItemAll(Map filterMap) {
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_ceshixm t1 where 1=1 ");
		queryString.append("select t1.id, t1.xiangmumc, t1.biaozunyq, t1.danwei, t1.shebeiyq from kl_ceshixm t1 where 1=1 " );
		if(null!=filterMap && !filterMap.isEmpty()){
			if(null!=filterMap.get("xiangmumc") && !"".equals(filterMap.get("xiangmumc"))){
				queryString.append(" and t1.xiangmumc like '%"+filterMap.get("xiangmumc")+"%'");
				countString.append(" and t1.xiangmumc like '%"+filterMap.get("xiangmumc")+"%'");
			}
			queryString.append(" and t1.id not in (select xiangmuid from kl_junyans_xm where junyansid='"+filterMap.get("junyansid")+"') ");
			countString.append(" and t1.id not in (select xiangmuid from kl_junyans_xm where junyansid='"+filterMap.get("junyansid")+"') ");
		}
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	@Override
	public PageResult querySelectItemSelect(Map filterMap) {
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_junyans_xm t1 left join kl_ceshixm t2 on t1.xiangmuid = t2.id where 1=1 ");
		queryString.append("select t1.id, t2.xiangmumc, t2.biaozunyq, t2.danwei, t2.shebeiyq from kl_junyans_xm t1 " +
				"left join kl_ceshixm t2 on t1.xiangmuid = t2.id where 1=1 " );
		queryString.append(" and t1.junyansid='"+filterMap.get("junyansid")+"' ");
		countString.append(" and t1.junyansid='"+filterMap.get("junyansid")+"' ");
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	public PageResult queryJunyansceshis(Map filterMap){
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_junyans_ceshis t1 " +
				"left join sys_users t2 on t1.userid=t2.userid " +
				"left join sys_organization t3 on t2.org_id=t3.org_id  where 1=1");
		queryString.append("select t1.id,t2.username,t2.sex,t2.zhichengname,t2.zhiwuname,t3.org_no,t1.userid from kl_junyans_ceshis t1 " +
				"left join sys_users t2 on t1.userid=t2.userid " +
				"left join sys_organization t3 on t2.org_id=t3.org_id  where 1=1 ");
		queryString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		countString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	public PageResult queryJunyansceshisyq(Map filterMap){
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_junyans_yq t1 " +
				"left join kl_yiqi t2 on t1.yiqiid=t2.id   where 1=1");
		queryString.append("select t1.id,t2.yiqilx,t2.yiqimc,t2.guigexh,t2.zhizaocs,t2.zhizaobh,t2.jingdudj from kl_junyans_yq t1 " +
				"left join kl_yiqi t2 on t1.yiqiid=t2.id where 1=1 ");
		queryString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		countString.append(" and t1.junyansid = '"+filterMap.get("junyansid")+"'");
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	@Override
	public PageResult querySelectYiqiAll(Map filterMap) {
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_yiqi t1  where 1=1 ");
		queryString.append("select t1.ID,t1.YIQILX,t1.YIQIMC,t1.GUIGEXH,t1.ZHIZAOCS from kl_yiqi t1 where 1=1 " );
		if(null!=filterMap && !filterMap.isEmpty()){
			if(null!=filterMap.get("yiqimc") && !"".equals(filterMap.get("yiqimc"))){
				queryString.append(" and t1.yiqimc like '%"+filterMap.get("yiqimc")+"%'");
				countString.append(" and t1.yiqimc like '%"+filterMap.get("yiqimc")+"%'");
			}
			queryString.append(" and t1.SHIYONGZT='维护' ");
			countString.append(" and t1.SHIYONGZT='维护' ");
		}
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	@Override
	public PageResult querySelectYiqiSelect(Map filterMap) {
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from kl_junyans_yq t1 left join kl_yiqi  t2 on t1.yiqiid = t2.id where 1=1 ");
		queryString.append("select t2.ID,t2.YIQILX,t2.YIQIMC,t2.GUIGEXH,t2.ZHIZAOCS from kl_junyans_yq t1 " +
				"left join kl_yiqi t2 on t1.yiqiid = t2.id where 1=1 " );
		queryString.append(" and t1.junyansid='"+filterMap.get("junyansid")+"' ");
		countString.append(" and t1.junyansid='"+filterMap.get("junyansid")+"' ");
		queryString.append(" order by t1.id desc");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}

	@Override
	public PageResult queryShiyandg(Map filterMap) {
		StringBuffer countString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();	
		countString.append("select count(*) from (  " );
		queryString.append("select t1.id,t1.file_name as dagangmc,t1.create_username as bianzhir,t1.create_time as bianzhirq,t2.shemidj,t2.id as junyansid,t1.id as attachid,t1.view_userid from tdm_attachment t1 " +
				"inner join kl_junyans t2 on t1.code_id=t2.shiyandg " );
		if(null!=filterMap && !filterMap.isEmpty()){
			if(null!=filterMap.get("dagangmc") && !"".equals(filterMap.get("dagangmc"))){
				queryString.append(" and t1.file_name like '%"+filterMap.get("dagangmc")+"%'");
			}
			queryString.append(" and (t1.create_userid = '"+filterMap.get("userid")+"' or ','||t1.view_userid||',' like '%,'||'"+filterMap.get("userid")+"'||',%') ");
		}
		queryString.append(" order by t1.id desc");
		countString.append(queryString.toString()+" ) ");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
	
	@Override
	public PageResult queryDataCenter(Map filterMap) {
		StringBuffer queryCountString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		queryString.append(" select * from ( ");
		queryString.append(" select t3.id as junyansid,t3.renwubh,t3.chanpindh,t3.shiyanlx,t3.fuzher,t3.shiyandd,t3.shiyankssj," +
				"to_char(t4.operatortime,'YYYY-MM-DD') as shiyanjssj,t1.flow_state_name,t2.add_user_name,t2.add_time,t3.lihuasqb," +
				"t3.lihuabg,t3.lihuasyzjbg,t5.file_name as dagangmc,t5.id as attachid,t3.qitacsbg from flow_task_info t1 " +
				"left join flow_task t2 on t1.flow_task_id=t2.id " +
				"left join kl_junyans t3 on t3.id=t2.main_id " +
				"left join flow_detailed t4 on t4.flow_task_id =t1.flow_task_id and t4.statusafterid = 'end' " +
				"left join TDM_ATTACHMENT t5 on t5.code_id=t3.shiyandg  "+
				"where t1.flow_state_id = 'end' and t1.state=0 " );
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("nodeid")&&!"".equals(filterMap.get("nodeid"))){
				String strSql = "select t.type_name,t.levelindex from tdm_file_document_type t where t.id='"+filterMap.get("nodeid")+"'";
				Query query = getSession().createSQLQuery(strSql);
				List lists = query.list();
				if (lists.size()>0){
					Object[] objs = (Object[])lists.get(0);
					String typename = objs[0].toString();
					String levelindex = objs[1].toString();
					if (!levelindex.equals("1")&&!levelindex.equals("2")){
						if (levelindex.equals("3")){
							queryString.append(" and t3.chanpindh = '"+typename+"'");
						}
						else if (levelindex.equals("4")){
							strSql = "select t2.type_name from tdm_file_document_type t1 left join tdm_file_document_type t2 on t2.id=t1.type_id where t1.id='"+filterMap.get("nodeid")+"'";
							query = getSession().createSQLQuery(strSql);
							List listdh = query.list();
							queryString.append(" and t3.chanpindh = '"+listdh.get(0)+"' and t3.jieduanlx = '"+typename+"'");
						}
					}
				}
			}
			if(null != filterMap.get("renwubh")&&!"".equals(filterMap.get("renwubh"))){
				queryString.append(" and t3.renwubh like '%"+filterMap.get("renwubh")+"%'");
			}
			if(null != filterMap.get("chanpindh")&&!"".equals(filterMap.get("chanpindh"))){
				queryString.append(" and t3.chanpindh like '%"+filterMap.get("chanpindh")+"%'");
			}
//			if(null != filterMap.get("jieduanlx")&&!"".equals(filterMap.get("jieduanlx"))){
//				queryString.append(" and t3.jieduanlx like '%"+filterMap.get("jieduanlx")+"%'");
//			}
			if(null != filterMap.get("starttime")&&!"".equals(filterMap.get("starttime"))){
				queryString.append(" and t3.shiyankssj >= '"+filterMap.get("starttime")+"'");
			}
			if(null != filterMap.get("endtime")&&!"".equals(filterMap.get("endtime"))){
				queryString.append(" and t3.shiyankssj <= '"+filterMap.get("endtime")+"'");
			}
		}
		
		//========================================================
		queryString.append(") ");
		queryCountString.append("select count(*) from ("+queryString+")");
		String sidx = (String) filterMap.get("sidx");
		String sord = (String) filterMap.get("sord");
		queryString.append(" order by " + sidx + " " + sord + " ");
		
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
}
